IF EXISTS (
	SELECT *
	FROM sysobjects
	WHERE id = object_id(N'dbo.spFdsNHumanReviewInject_Details')
	AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

	BEGIN
		
		PRINT 'Dropping spFdsNHumanReviewInject_Details'
        DROP PROCEDURE dbo.spFdsNHumanReviewInject_Details
        
	END

GO
	
/*******************************************************************************
* Name:					spFdsNHumanReviewInject_Details
* Purpose:				Injects new RevReviewBoardDetail records from the staging
*						table and updates the staging table with the newly
*						inserted ids.  It also handles insertions into the 
*						review notifcation table and the annotation subsystem,
*						where appropriate.
*
* PARAMETERS
* Name    				Description					
* -------------  		--------------------------------------------------------
* N/A
* ERRORS USED			Description
* -------------			--------------------------------------------------------
* 
*
* RETURN VALUE
* Value    				Description					
* --------------		--------------------------------------------------------
*  @_exitStatus		An exit status of either true or false
********************************************************************************/
CREATE PROCEDURE spFdsNHumanReviewInject_Details

AS

BEGIN

	SET NOCOUNT ON

	-- declare local variables
	DECLARE @_stagedBoardReviewDetailId		AS INTEGER
	DECLARE @_boardReviewDetailId			AS INTEGER
	DECLARE @_revBoardReviewId				AS INTEGER
	DECLARE @_meetingId						AS INTEGER
	DECLARE @_rexDetailId					AS INTEGER
	
	
	
	
	
	-- declare/set "constants"
	
	DECLARE @_TRUE				AS INTEGER
	DECLARE @_SUCCESS			AS BIT
	DECLARE @_FAILURE			AS BIT
	
	
	SET @_TRUE = 1
	SET @_SUCCESS = 1
	SET @_FAILURE = 0
	
	--- select the set of new reviews and open up a cursor on that data
	DECLARE cursorNewBoardReviewDetailIds CURSOR LOCAL FAST_FORWARD FOR
		SELECT		detail.Id
					,board.RevBoardReviewId
					,detail.RevBoardMeetingId
					,detail.RexBoardReviewDetailId
		FROM		FdsProtocolStagingProtocolReview ReviewStaging
		INNER JOIN	FdsProtocolStagingBoardReview board
			ON		board.RexReviewId = ReviewStaging.RexReviewId
			AND		board.RexProtocolId = ReviewStaging.RexProtocolId
		INNER JOIN	FdsProtocolStagingBoardReviewDetail detail
			ON		detail.RexProtocolId = board.RexProtocolId
			AND		detail.RexReviewId = board.RexReviewId
			AND		detail.RexMailGroupId = board.RexMailGroupId
		WHERE		detail.RevBoardReviewDetailId IS NULL
	
	BEGIN TRANSACTION spFdsNHumanReviewInject_Details
	
	-- iterate over each new board review detail inserting the data to the review
	-- table and updating the staging table with the new board review detail ID
	OPEN cursorNewBoardReviewDetailIds
	FETCH NEXT FROM cursorNewBoardReviewDetailIds INTO @_stagedBoardReviewDetailId
		, @_revBoardReviewId
		, @_meetingId
		, @_rexDetailId
	WHILE @@FETCH_STATUS = 0
  	BEGIN
		    
		-- insert the new board review detail data
		INSERT INTO RevBoardReviewDetail (
				DetailDate
				,RevStatusTypeId
				,RevBoardReviewId)
		SELECT	BoardReviewDetailStaging.DetailDate
				,BoardReviewDetailStaging.RevStatusTypeId
				,@_revBoardReviewId
		FROM	FdsProtocolStagingBoardReviewDetail BoardReviewDetailStaging
		WHERE	BoardReviewDetailStaging.Id = @_stagedBoardReviewDetailId
			
		IF @@ERROR > 0 GOTO ERROR_HANDLER
		
		SET @_boardReviewDetailId = SCOPE_IDENTITY()
		
		-- udpate the staging table with the new board review detail ID
		UPDATE	FdsProtocolStagingBoardReviewDetail
  		SET		RevBoardReviewDetailId = @_boardReviewDetailId
  		WHERE	Id = @_stagedBoardReviewDetailId
  		
  		-- insert the REX board review detail data (used for future matching)
		INSERT INTO RevRexBoardReviewDetail (RevBoardReviewDetailId, RexBoardReviewDetailId)
		VALUES		(@_boardReviewDetailId, @_rexDetailId)
  		
  		IF @@ERROR > 0 GOTO ERROR_HANDLER
  		
  		-- insert notification information (if any)
  		IF (SELECT	NotificationDate
  			FROM	FdsProtocolStagingBoardReviewDetail detail
  			WHERE	detail.Id = @_stagedBoardReviewDetailId) IS NOT NULL
  			BEGIN
  			
  				-- insert the review notification data
				INSERT INTO RevReviewNotification (
						RevBoardReviewDetailId
						,Notification
						,NotificationDate
						,ResponseDate )
				SELECT	BoardReviewDetailStaging.RevBoardReviewDetailId
						,BoardReviewDetailStaging.AnnotationDetail
						,BoardReviewDetailStaging.NotificationDate
						,BoardReviewDetailStaging.ResponseDate
				FROM	FdsProtocolStagingBoardReviewDetail BoardReviewDetailStaging
				WHERE	BoardReviewDetailStaging.Id = @_stagedBoardReviewDetailId
					
				IF @@ERROR > 0 GOTO ERROR_HANDLER
  			
  			END
		
		-- if it's a meeting review, insert the meeting mapping.
		IF @_meetingId IS NOT NULL
		BEGIN
			
			-- insert the review meeting data
			INSERT INTO RevReviewMeeting (RevBoardReviewDetailId, RevBoardMeetingId)
			VALUES		(@_boardReviewDetailId, @_meetingId)
				
			IF @@ERROR > 0 GOTO ERROR_HANDLER
			
		END
			
		

      	-- get next board review ID from list
		FETCH NEXT FROM cursorNewBoardReviewDetailIds INTO @_stagedBoardReviewDetailId
			, @_revBoardReviewId
			, @_meetingId
			, @_rexDetailId
	
   	END
   	
   	-- close and deallocate cursor
	CLOSE cursorNewBoardReviewDetailIds
  	DEALLOCATE cursorNewBoardReviewDetailIds
  	
  	SET NOCOUNT OFF
   	
   	COMMIT TRANSACTION spFdsNHumanReviewInject_Details
	RETURN @_SUCCESS

ERROR_HANDLER:
	BEGIN
		ROLLBACK TRANSACTION spFdsNHumanReviewInject_Details
		RETURN @_FAILURE
	END
END 